package com.smart.stock.common.util.h2;

import com.smart.stock.common.util.spring.EntityUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.bson.Document;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.BasicQuery;
import org.springframework.data.mongodb.core.query.Query;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.List;
import java.util.Vector;

@Slf4j
public class H2Util<T> {
	private static String targetSchema = "andrew";
	private static int defaultStringColumnSize = 200;

	public static int execNoQuerySQL(String sql) throws SQLException{
		Connection conn = H2ConnectionPool.getConnection();
		Statement stmt = conn.createStatement();
		int i = stmt.executeUpdate(sql);
		H2ConnectionPool.returnConnection(conn);
		return i;
	}

	public static ResultSet execQuerySQL(String sql) throws SQLException{
		Connection conn = H2ConnectionPool.getConnection();
		Statement stmt = conn.createStatement();
		ResultSet resultSet = stmt.executeQuery(sql);
		H2ConnectionPool.returnConnection(conn);
		return resultSet;
	}


	public static boolean tableExist(String tableName) throws SQLException{
		Connection conn = H2ConnectionPool.getConnection();
		Statement stmt = conn.createStatement();

		String sql = String.format("SELECT COUNT(*) AS cnt FROM information_schema.TABLES WHERE table_name = '%s'",tableName.toUpperCase());
		log.debug("判断表是否存在："+sql);
		ResultSet rs = stmt.executeQuery(sql);
		boolean tableExist = false;
		while (rs.next()) {
			Object object = rs.getObject(1);
			if (Integer.valueOf(object.toString())>0) {
				tableExist = true;
			}
		}
		H2ConnectionPool.returnConnection(conn);
		return tableExist;
	}



	public static void printTables() throws Exception{
		ResultSet rs = execQuerySQL("SELECT * FROM information_schema.TABLES");
		ResultSetMetaData rsmd= rs.getMetaData();
		int m = rsmd.getColumnCount();

		//打印所有字段
		for (int i = 1; i <= m; i++) {
			String columnName = rsmd.getColumnName(i);
			System.out.print(columnName+",");
		}
		System.out.println();

		//打印所有行
		while (rs.next()) {
			Vector v=new Vector();
			for (int i = 1; i <= m; i++) {
				v.add(rs.getObject(i));
			}
			System.out.println(v.toString().replace("[", "").replace("]", ""));
		}
	}
	public static void printTableDatas(String tableName) throws Exception{
		ResultSet rs = execQuerySQL("SELECT * FROM "+tableName +" limit 10");
		ResultSetMetaData rsmd= rs.getMetaData();
		int m = rsmd.getColumnCount();

		//打印所有字段
		for (int i = 1; i <= m; i++) {
			String columnName = rsmd.getColumnName(i);
			System.out.print(columnName+",");
		}
		System.out.println();

		//打印所有行
		while (rs.next()) {
			Vector v=new Vector();
			for (int i = 1; i <= m; i++) {
				v.add(rs.getObject(i));
			}
			System.out.println(v.toString().replace("[", "").replace("]", ""));
		}
	}


	public static <T> List<T> createListFromMongoByCode(MongoTemplate template, Class<T> entityClass, String code) throws Exception {
		return createListFromMongo(template,entityClass,code,null);
	}

	public static <T> List<T> createListFromMongoByDate(MongoTemplate template, Class<T> entityClass, Integer tradeDate) throws Exception {
		return createListFromMongo(template,entityClass,null,tradeDate);
	}

	public static <T> List<T> createListFromMongo(MongoTemplate template, Class<T> entityClass, String code, Integer tradeDate) throws Exception {
		Document dbObject = new Document();
		Document fieldObject = new Document();
		if (StringUtils.isNotBlank(code)) {
			dbObject.put("code",code);
		}
		if (tradeDate != null) {
			dbObject.put("tradeDate",tradeDate);
		}
		Query query = new BasicQuery(dbObject, fieldObject);
		List<T> list = template.find(query, entityClass);//StockPriceDaily.class
		return list;
	}

	public static <T> String createH2TableFromMongo(MongoTemplate template, Class<T> entityClass,String code) throws Exception {
		String entityName = EntityUtil.getEntityNameByCode(entityClass,code);
		List<T> list = createListFromMongoByCode(template, entityClass,code);
		String h2Table = createH2TableFromList(list,entityName);
		return h2Table;
	}

//	public static <T> String createH2TableFromMongo(MongoTemplate template, Class<T> entityClass) throws Exception {
//		List<T> list = createListFromMongo(template, entityClass);
//		String h2Table = createH2TableFromList(list);
//		return h2Table;
//	}

	/**
	 * 根据mongodb返回的数据，构建h2数据库中的表
	 * @param list
	 * @return
	 * @throws Exception
	 */
	public static <T> String createH2TableFromList(List<T> list) throws Exception{
		Class<?> clazz = list.get(0).getClass();
		String clazzName = clazz.getSimpleName();
		createH2TableFromList(list,clazzName);
		return clazzName;
	}

	public static <T> String createH2TableFromList(List<T> list,String tableName) throws Exception{
		Class<?> clazz = list.get(0).getClass();
		String clazzName = tableName;
		Connection conn = H2ConnectionPool.getConnection();
		Statement stmt = conn.createStatement();
		String createColumnStr = "";
		String insertColumnStr = "";
		int rowCount = 0;
		long startTime = System.currentTimeMillis();
		PreparedStatement pstmt = null;


		//根据第一条记录，获取字段信息
		T clazz0 = (T)list.get(0);
		Field[] fields0 = clazz.getDeclaredFields();
//		Object fieldValue = null;

		//拼接columnStr，根据字段名称、类型、数值
		for (int i1 = 0; i1 < fields0.length; i1++) {
			Field field = fields0[i1];
			field.setAccessible(true);//在反射时使属性可以被访问，即时是private权限的属性
			String fieldName = field.getName();
			String fieldType = field.getType().getSimpleName();
			Object fieldValue = field.get(clazz0);
			System.out.println("属性名:" + fieldName + "属性名:" + fieldType + " 属性值:" + fieldValue);
			String column_type = ColumnTypeUtil.java2h2(fieldType);
			if (column_type.equals("VARCHAR")) {
				column_type = column_type+"("+defaultStringColumnSize+")";
			}
			createColumnStr += ",\n"+fieldName+" "+column_type;
			insertColumnStr += ",?";
//					insertColumnStr += ","+fieldValue;
		}

		insertColumnStr = insertColumnStr.replaceFirst(",", "");
		createColumnStr = createColumnStr.replaceFirst(",", "");

//		//如果h2库中存在，则从不再计算新高数据
//		if (tableExist(clazzName)) {
//			return clazzName;
//		}


		//构建drop，create的sql语句
		String sqlDrop = "drop table if exists "+clazzName;
		System.out.println(sqlDrop);
		stmt.executeUpdate(sqlDrop);
		String sqlCreate = "create table "+clazzName+" ("+createColumnStr+");";
		System.out.println(sqlCreate);
		stmt.executeUpdate(sqlCreate);
		log.info("printTables...");
		printTables();



		//构建insert的sql语句
		String sqlInsert = "insert into "+clazzName+" values("+insertColumnStr+")";
		System.out.println(sqlInsert);
		pstmt = conn.prepareStatement(sqlInsert);

		//根据传入的list，批量插入数据
		for (int i = 0; i < list.size(); i++) {
			T clazzT = list.get(i);
			Field[] fieldsT = clazzT.getClass().getDeclaredFields();
			//遍历字段
			for (int i1 = 0; i1 < fieldsT.length; i1++) {
				Field fieldT = fieldsT[i1];
				fieldT.setAccessible(true);//在反射时使属性可以被访问，即时是private权限的属性
				Object fieldValue = fieldT.get(clazzT);
				pstmt.setObject(i1+1, fieldValue);//index从1开始
			}
			pstmt.addBatch();
			rowCount++;
			if (rowCount%1000 == 0) {
				pstmt.executeBatch();
				pstmt.clearBatch();
				conn.commit();

				long endTime = System.currentTimeMillis();
				long time = (endTime-startTime);
				System.out.println("rowCount:"+rowCount+",time(ms):"+time);
				startTime = endTime;
			}

//				stmt.execute(sqlInsert);
		}

		pstmt.executeBatch();
		pstmt.clearBatch();
		conn.commit();
		System.out.println("rowCount:"+rowCount+",time(ms):"+(System.currentTimeMillis()-startTime));
		printTableDatas(clazzName);
		H2ConnectionPool.returnConnection(conn);
		//todo /big-screen/getIndexTengLuoXian
		//腾落线
		return clazzName;
	}


	public static <T> String insertH2TableFromList(List<T> list,String tableName) throws Exception{
		Class<?> clazz = null;
		try {
			clazz = list.get(0).getClass();
		} catch (Exception e) {
			e.printStackTrace();
		}

		String clazzName = tableName;
		Connection conn = H2ConnectionPool.getConnection();
//		Statement stmt = conn.createStatement();
		String columnStr = "";
		String s = "";
		int rowCount = 0;
		long startTime = System.currentTimeMillis();
		PreparedStatement pstmt = null;

		//根据第一条记录，获取字段信息
		T clazz0 = (T)list.get(0);
		Field[] fields0 = clazz.getDeclaredFields();
//		Object fieldValue = null;

		//拼接columnStr，根据字段名称、类型、数值
		for (int i1 = 0; i1 < fields0.length; i1++) {
			Field field = fields0[i1];
			field.setAccessible(true);//在反射时使属性可以被访问，即时是private权限的属性
			String fieldName = field.getName();
			String fieldType = field.getType().getSimpleName();
			Object fieldValue = field.get(clazz0);
			System.out.println("属性名:" + fieldName + "属性名:" + fieldType + " 属性值:" + fieldValue);
			String column_type = ColumnTypeUtil.java2h2(fieldType);
			if (column_type.equals("VARCHAR")) {
				column_type = column_type+"("+defaultStringColumnSize+")";
			}
			columnStr += ",\n"+fieldName+" "+column_type;
			s += ",?";
//					s += ","+fieldValue;
		}
		s = s.replaceFirst(",", "");

		//构建drop，create的sql语句
//		String sqlDrop = "drop table if exists "+clazzName;
//		columnStr = columnStr.replaceFirst(",", "");

//		String sqlCreate = "create table "+clazzName+" ("+columnStr+");";
//		System.out.println(sqlDrop);
//		System.out.println(sqlCreate);

//		if (tableExist(clazzName)) {
//			log.info(clazzName+"：表已存在，不再重建和更新数据");
//			return clazzName;
//		}

//		stmt.executeUpdate(sqlDrop);
//		stmt.executeUpdate(sqlCreate);
//		printTables();

		//构建insert的sql语句
		String sqlInsert = "insert into "+clazzName+" values("+s+")";
		System.out.println(sqlInsert);
		pstmt = conn.prepareStatement(sqlInsert);

		//根据传入的list，批量插入数据
		for (int i = 0; i < list.size(); i++) {
			T clazzT = list.get(i);
			Field[] fieldsT = clazzT.getClass().getDeclaredFields();
			//遍历字段
			for (int i1 = 0; i1 < fieldsT.length; i1++) {
				Field fieldT = fieldsT[i1];
				fieldT.setAccessible(true);//在反射时使属性可以被访问，即时是private权限的属性
				Object fieldValue = fieldT.get(clazzT);
				pstmt.setObject(i1+1, fieldValue);//index从1开始
			}
			pstmt.addBatch();
			rowCount++;
			if (rowCount%1000 == 0) {
				pstmt.executeBatch();
				pstmt.clearBatch();
				conn.commit();

				long endTime = System.currentTimeMillis();
				long time = (endTime-startTime);
				System.out.println("rowCount:"+rowCount+",time(ms):"+time);
				startTime = endTime;
			}

//				stmt.execute(sqlInsert);
		}

		pstmt.executeBatch();
		pstmt.clearBatch();
		conn.commit();
		System.out.println("rowCount:"+rowCount+",time(ms):"+(System.currentTimeMillis()-startTime));
		printTableDatas(clazzName);
		H2ConnectionPool.returnConnection(conn);
		//todo /big-screen/getIndexTengLuoXian
		//腾落线
		return clazzName;
	}


	public static <T> List<T> getTableFromMongo(MongoTemplate template, Class<T> entityClass) throws Exception {
		Document dbObject = new Document();
		Document fieldObject = new Document();
		Query query = new BasicQuery(dbObject, fieldObject);
		List<T> list = template.find(query, entityClass);//StockPriceDaily.class
		return list;
	}

	public static void main(String[] args) throws SQLException {
		ResultSet rs = execQuerySQL("SELECT * FROM information_schema.TABLES");
		ResultSetMetaData rsmd= rs.getMetaData();
		int m = rsmd.getColumnCount();

		//打印所有字段
		for (int i = 1; i <= m; i++) {
			String columnName = rsmd.getColumnName(i);
			System.out.print(columnName+",");
		}
		System.out.println();

		//打印所有行
		while (rs.next()) {
			Vector v=new Vector();
			for (int i = 1; i <= m; i++) {
				v.add(rs.getObject(i));
			}
			System.out.println(v.toString().replace("[", "").replace("]", ""));
		}
	}
}
